﻿--提取职级异动一级审批列表
CREATE PROCEDURE [dbo].[proc_Employee_GradeMove_GetApplyFList]
(
	@startIndex int,
	@endIndex int,
	@ename nvarchar(50),
	@companyid int,
	@audit nvarchar(4)
)
as
Begin
		;WITH list As(Select ROW_NUMBER() OVER (Order By EE.Id ASC)AS Row,
			EE.Id,
			(select ENumber from Employee E where EE.EId=E.Id) as ENumber,
			(select ETempNumber from Employee E where EE.EId=E.Id) as ETempNumber,
			EE.EId,
			(select EName from Employee E where EE.EId=E.Id) as EName,
			(select Sex from Employee E where EE.EId=E.Id) as Sex,
			(select CompanyName from Company where id=(select companyid from employee E where EE.EId=E.Id)) as CompanyName,
			(select DName from Department where id=(select departmentid from employee E where EE.EId=E.Id)) as DName,
			(select PostName from Post P where id=(select PostId from employee E where EE.EId=E.Id)) as PostName,
			(select Title from Grade G where id=ee.AGradeId) as GradeName,
			EE.FirstAudit,
			EE.FirstAuditPerson,
			EE.FirstAuditTime,
			EE.SecondAudit,
			EE.SecondAuditPerson,
			EE.SecondAuditTime
			from employee_Grade EE inner join Employee E on EE.EId=E.Id
			where
				 e.ename like '%'+@ename+'%'
			 and (@CompanyId=0 Or E.Companyid = @CompanyId)
			 and ee.Proposer is not null
			 and (case when ee.firstaudit=1 then '1' 
				   when ee.firstaudit=0 and ee.firstauditperson is null then '0'
				   when ee.firstaudit=0 and ee.firstauditperson is not null then '1' end) like '%'+@audit+'%'
			and  e.state <> 5
		)
		Select * From list Where Row between @StartIndex and @EndIndex
	End


RETURN
